package org.jivesoftware.openfire.register;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.openfire.dynamic.QuestionBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class DbRegisterManager {
	private DbRegisterManager() {
    }
	public static DbRegisterManager getInstance() {
        return RegisterManager;		
	}
	private static final Logger log = LoggerFactory.getLogger(DbRegisterManager.class);
	private static final DbRegisterManager RegisterManager = new DbRegisterManager();
	private static final String INSER_ACCOUNT="INSERT INTO ofUser (username, plainPassword, name, email, creationDate, modificationDate) VALUES (?,?,?,?,?,?)";
	private static final String INSER_GROUP="INSERT INTO ofgroupuser(groupName,username,administrator) VALUES(?,?,?)";
	private static final String QUERY_ALREADYUSER = "select  count(*) AS rowcount from ofgroupuser where groupName = ? and username = ?";
	public static Connection getConnection(){
		Connection con = null;
		 try {		
			 con = DbConnectionManager.getConnection();
			
		}
		 catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			log.debug("DriverManager.getConnection(url,user,pwd) failed");
		}
		return con;
	}
	public HashMap<String,Object> queryAlreadyUser(String groupName,String username){
		Connection con = getConnection();
        PreparedStatement pstmt = null;
        HashMap<String, Object> result = new HashMap<String, Object>();
        try {
            pstmt = con.prepareStatement(QUERY_ALREADYUSER);
            int i =1;
            pstmt.setString(i++, groupName);
            pstmt.setString(i++, username);
            ResultSet rs = pstmt.executeQuery();
           
            int rowcount = 0 ;
            while (rs.next()) {  
            	rowcount = rs.getInt("rowcount");

            }
            result.put("rowcount",rowcount);
            log.info("rowcount:"+rowcount);
            return result;
        } catch (SQLException sqle) {
            log.info("queryAlreadyUser exception: {}", sqle);
            return result;
        } finally {
        		 DbConnectionManager.closeConnection(pstmt, con);
        }
}
	public boolean addAccount(AccountBean accountBean){
		  Connection con = getConnection();
	        PreparedStatement pstmt = null;
	        try {
	            pstmt = con.prepareStatement(INSER_ACCOUNT);
	            int i = 1;
	         
	            pstmt.setString(i++,accountBean.getUserName());
	            pstmt.setString(i++,accountBean.getPlainPassword());
	            pstmt.setString(i++,accountBean.getName());
	            pstmt.setString(i++,accountBean.getEmail());
	            pstmt.setString(i++,accountBean.getCreationDate());
	            pstmt.setString(i++,accountBean.getModificationDate());
	            return pstmt.execute();
	        } catch (SQLException sqle) {
	            log.info("addAccount add exception: {}", sqle);
	            return false;
	        } finally {
	        		 DbConnectionManager.closeConnection(pstmt, con);
	        }
	}
	public boolean addGroup(GroupUserBean groupUserBean){
		  Connection con = getConnection();
	        PreparedStatement pstmt = null;
	        try {
	            pstmt = con.prepareStatement(INSER_GROUP);
	            int i = 1;
	         
	            pstmt.setString(i++,groupUserBean.getGroupName());
	            pstmt.setString(i++,groupUserBean.getUserName());
	            pstmt.setBoolean(i++,groupUserBean.getAdministrator());
	       
	            return pstmt.execute();
	        } catch (SQLException sqle) {
	            log.info("addGroup add exception: {}", sqle);
	            return false;
	        } finally {
	        		 DbConnectionManager.closeConnection(pstmt, con);
	        }
	}
	
}
